package com.test.shiver.dao;
import com.test.shiver.entity.User;
import com.test.shiver.util.JDBCUtils;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.sql.ResultSet;

public class UserDao
{
    public UserDao(){

    }

    public List<User> selectAll(){

            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            List<User> userList = null;
                   try {
                    // 获得连接:
                    conn = JDBCUtils.getConnection();
                    // 编写SQL:
                    String sql = "select id,username,password from t_user;";
                    // 预编译SQL:
                    pstmt = conn.prepareStatement(sql);
                    // 设置参数
                    // 执行SQL:
                    rs = pstmt.executeQuery();
                    userList = new ArrayList<User>();
                    while (rs.next()) {
                        User user = new User();
                        user.id = rs.getInt("id");
                        user.username = rs.getString("username");
                        user.password = rs.getString("password");
                        userList.add(user);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    JDBCUtils.release(rs, pstmt, conn);
                }

                return userList;
    }

    public User find(int id){

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<User> userList = null;
        try {
            // 获得连接:
            conn = JDBCUtils.getConnection();
            // 编写SQL:
            String sql = "select id,username,password from t_user where id = ?";
            // 预编译SQL:
            pstmt = conn.prepareStatement(sql);
            // 设置参数
            pstmt.setInt(1,id);
            // 执行SQL:
            rs = pstmt.executeQuery();
            userList = new ArrayList<User>();
            while (rs.next()) {
                User user = new User();
                user.id = rs.getInt("id");
                user.username = rs.getString("username");
                user.password = rs.getString("password");
                userList.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(rs, pstmt, conn);
        }

        return userList.get(0);
    }

    public int insert(User user){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = -1;
        List<User> userList = null;
        try {
            // 获得连接:
            conn = JDBCUtils.getConnection();
            // 编写SQL:
            String sql = "insert into t_user (username, password) VALUES (?,?);";
            // 预编译SQL:
            pstmt = conn.prepareStatement(sql);
            // 设置参数
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            // 执行SQL:
            result = pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(null, pstmt, conn);
        }

        return result;
    }

    public int update(User user){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = -1;
        try {
            // 获得连接:
            conn = JDBCUtils.getConnection();
            // 编写SQL:
            String sql = "update t_user set username=?, password=? where id=?";
            System.out.println("sql:"+sql);
            // 预编译SQL:
            pstmt = conn.prepareStatement(sql);
            // 设置参数
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            System.out.println("pwd:"+user.getPassword());
            pstmt.setInt(3, user.getId());
            // 执行SQL:
            result = pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(null, pstmt, conn);
        }

        return result;
    }

    public int delete(int id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = -1;
        List<User> userList = null;
        try {
            // 获得连接:
            conn = JDBCUtils.getConnection();
            // 编写SQL:
            String sql = "delete from t_user where id = ?;";
            // 预编译SQL:
            pstmt = conn.prepareStatement(sql);
            // 设置参数
            pstmt.setInt(1, id);
            // 执行SQL:
            result = pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(null, pstmt, conn);
        }

        return result;
    }

    public static void main(String[] args) {
        UserDao userDao = new UserDao();
//        List<User> userList =   userDao.selectAll();
//
//        if(userList == null || userList.size() == 0){
//            return;
//        }
//
//        for (User user:userList) {
//            System.out.println(user.username);
//            System.out.println(user.password);
//        }
        userDao.delete(1);

    }
}

